{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "from web3 import Web3\n",
    "import pickle\n",
    "import base64\n",
    "import solcx\n",
    "from solcx import compile_source\n",
    "import random\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = '0x800495d9000000000000007d94288c0454797065948c0c56656869636c652044617461948c10436f6e74726163745f61646472657373948c2a307837313446654639363832303364306230303231393962373330334136666234393061343036434331948c055072696365944740000000000000008c0446696c65948c2e516d56435a654e523765514e45753547656b7171626e6d6b3835763636634648486a5a5a54475a78417141326844948c0b4465736372697074696f6e945d948c2173706565642c6770732c73656e736f722c6163636964656e742c6e6574776f726b9461752e'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = Web3.toBytes(hexstr=data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = pickle.loads(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "b = json.dumps(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"Type\": \"Vehicle Data\", \"Contract_address\": \"0x714FeF968203d0b002199b7303A6fb490a406CC1\", \"Price\": 2.0, \"File\": \"QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD\", \"Description\": [\"speed,gps,sensor,accident,network\"]}'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w3 = Web3(Web3.IPCProvider('data/geth.ipc'))\n",
    "w3.isConnected()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Account:0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8, Balance:10264.00855744799988 ether\n",
      "Account:0xbDcB2416e02b9e4f7714D6546606af08764852D2, Balance:2100 ether\n",
      "Account:0xdE3483A190E03F15AF59e9BC2f134764fa4e03A8, Balance:99.998288508 ether\n",
      "Account:0xA70d15002602ccbBe7853c997FfeD6CE68900105, Balance:99.998288444 ether\n",
      "Account:0x090a1E9196aEA05B6223A9D1D6316A29b4DCE571, Balance:99.998288512 ether\n",
      "Account:0xfAC4ACDFBc77fd115cd4b8C05a074dBECba4377D, Balance:99.998288508 ether\n",
      "Account:0x8D7813a11703319452b7Ab54F3C3C98654F6A52c, Balance:99.99828858 ether\n"
     ]
    }
   ],
   "source": [
    "for account in w3.eth.accounts:\n",
    "    print(\"Account:{}, Balance:{} ether\".format(account, Web3.fromWei(w3.eth.get_balance(account), 'ether')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(2,7):\n",
    "    w3.geth.personal.unlockAccount(w3.eth.accounts[0], 'abc12345')\n",
    "    w3.eth.sendTransaction({'from':w3.eth.accounts[0],'to':w3.eth.accounts[i],'value':Web3.toWei(100, 'ether')})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "w3.geth.miner.start(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "w3.geth.miner.stop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>模拟车辆OBD生成合约并写入交易</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compile_source_file(file_path):\n",
    "    with open(file_path, 'r') as f:\n",
    "        source = f.read()\n",
    "    return compile_source(source)\n",
    " \n",
    "def deploy_contract(address, contract_interface):\n",
    "    contract = w3.eth.contract(\n",
    "        address=address,\n",
    "        abi=contract_interface['abi'],\n",
    "        bytecode=contract_interface['bin']).constructor().transact()\n",
    "    return contract"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_hash_list = []\n",
    "for i in range(2,7):\n",
    "    w3.eth.default_account=w3.eth.accounts[i]\n",
    "    w3.geth.personal.unlockAccount(w3.eth.accounts[i], 'abc12345')\n",
    "    contract_source_path = 'getpass.sol'\n",
    "    compiled_sol = compile_source_file('getpass.sol')\n",
    "    contract_id, contract_interface = compiled_sol.popitem()\n",
    "    tx_hash_list.append(deploy_contract(w3.eth.accounts[i], contract_interface))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "contract_address_list = []\n",
    "for tx_hash in tx_hash_list:\n",
    "    contract_address_list.append(w3.eth.get_transaction_receipt(tx_hash)['contractAddress'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "block_filter = w3.eth.filter('latest')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(2,7):\n",
    "    w3.geth.personal.unlockAccount(w3.eth.accounts[i], 'abc12345')\n",
    "    metadata = {}\n",
    "    metadata['Type'] = 'Vehicle Data'\n",
    "    metadata['VIN'] = str(hash('Vehicle_'+str(i)))\n",
    "    metadata['Location'] = 'Guangzhou'\n",
    "    metadata['Record_num'] = random.randint(1000,10000)\n",
    "    metadata['Contract_address'] = contract_address_list[i-2]\n",
    "    metadata['Price'] = random.uniform(0,2)\n",
    "    metadata['File'] = 'QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD'\n",
    "    metadata['Description'] = ['speed,gps,sensor,accident,network']\n",
    "    m = b'\\xFF\\xFF' + pickle.dumps(metadata)\n",
    "    tx_hash = w3.eth.sendTransaction({'from':w3.eth.accounts[i],'to':w3.eth.accounts[i],'value':Web3.toWei(0, 'ether'), 'data':m})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "entries = block_filter.get_new_entries()\n",
    "for entry in entries:\n",
    "    tx_counts = w3.eth.get_block_transaction_count(entry)\n",
    "    for i in range(tx_counts):\n",
    "        tx = w3.eth.get_transaction_by_block(entry, i)\n",
    "        if tx['input'][:6]=='0xffff':\n",
    "            data = Web3.toBytes(hexstr=tx['input'][6:])\n",
    "            data = pickle.loads(data)\n",
    "            sqlstr = \"INSERT INTO transaction(from_addr,to_addr,vin_id,file,\"+\\\n",
    "                \"location,record_num,metadata,contract_addr,price,rawdata,status) values(\"+\\\n",
    "                \"'\"+tx['from']+\"',\"+\\\n",
    "                \"'\"+tx['to']+\"',\"+\\\n",
    "                \"'\"+data['VIN']+\"',\"+\\\n",
    "                \"'\"+data['File']+\"',\"+\\\n",
    "                \"'\"+data['Location']+\"',\"+\\\n",
    "                str(data['Record_num'])+\",\"+\\\n",
    "                \"'\"+','.join(data['Description'])+\"',\"+\\\n",
    "                \"'\"+data['Contract_address']+\"',\"+\\\n",
    "                str(data['Price'])+\",\"+\\\n",
    "                \"'\"+str(base64.b64encode(pickle.dumps(tx)), encoding='utf-8')+\"','New')\"\n",
    "            cursor.execute(sqlstr)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = \"INSERT INTO transaction(from_addr,to_addr,vin_id,file,location,record_num,metadata,contract_addr,price,rawdata,status) values('0xdE3483A190E03F15AF59e9BC2f134764fa4e03A8','0xdE3483A190E03F15AF59e9BC2f134764fa4e03A8','-7807955977915799603','QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD','Guangzhou',9624,'speed,gps,sensor,accident,network','0x6bB0c13AA3bfCd998193C1198E0C40A4f0e1ea41',0.4809053012430742,'gASVPQQAAAAAAACME3dlYjMuZGF0YXN0cnVjdHVyZXOUjA1BdHRyaWJ1dGVEaWN0lJOUKYGUfZQojAlibG9ja0hhc2iUjA1oZXhieXRlcy5tYWlulIwISGV4Qnl0ZXOUk5RDIPimBxfIQXGlMLBqKD4R58IfvLeyHV63NBrE17ShDOBmlIWUgZSMC2Jsb2NrTnVtYmVylE3+F4wEZnJvbZSMKjB4ZEUzNDgzQTE5MEUwM0YxNUFGNTllOUJDMmYxMzQ3NjRmYTRlMDNBOJSMA2dhc5RKUCYCAIwIZ2FzUHJpY2WUSgDKmjuMBGhhc2iUaAhDIJYwuveZrdBUz+rfZS9l/ZuyJjnUODw7tiOIor83E6j5lIWUgZSMBWlucHV0lFhWAgAAMHhmZmZmODAwNDk1MWQwMTAwMDAwMDAwMDAwMDdkOTQyODhjMDQ1NDc5NzA2NTk0OGMwYzU2NjU2ODY5NjM2YzY1MjA0NDYxNzQ2MTk0OGMwMzU2NDk0ZTk0OGMxNDJkMzczODMwMzczOTM1MzUzOTM3MzczOTMxMzUzNzM5MzkzNjMwMzM5NDhjMDg0YzZmNjM2MTc0Njk2ZjZlOTQ4YzA5NDc3NTYxNmU2NzdhNjg2Zjc1OTQ4YzBhNTI2NTYzNmY3MjY0NWY2ZTc1NmQ5NDRkOTgyNThjMTA0MzZmNmU3NDcyNjE2Mzc0NWY2MTY0NjQ3MjY1NzM3Mzk0OGMyYTMwNzgzNjYyNDIzMDYzMzEzMzQxNDEzMzYyNjY0MzY0MzkzOTM4MzEzOTMzNDMzMTMxMzkzODQ1MzA0MzM0MzA0MTM0NjYzMDY1MzE2NTYxMzQzMTk0OGMwNTUwNzI2OTYzNjU5NDQ3M2ZkZWM3MjcwNzUzZjg1NDhjMDQ0NjY5NmM2NTk0OGMyZTUxNmQ1NjQzNWE2NTRlNTIzNzY1NTE0ZTQ1NzUzNTQ3NjU2YjcxNzE2MjZlNmQ2YjM4MzU3NjM2MzY2MzQ2NDg0ODZhNWE1YTU0NDc1YTc4NDE3MTQxMzI2ODQ0OTQ4YzBiNDQ2NTczNjM3MjY5NzA3NDY5NmY2ZTk0NWQ5NDhjMjE3MzcwNjU2NTY0MmM2NzcwNzMyYzczNjU2ZTczNmY3MjJjNjE2MzYzNjk2NDY1NmU3NDJjNmU2NTc0Nzc2ZjcyNmI5NDYxNzUyZZSMBW5vbmNllEsDjAJ0b5SMKjB4ZEUzNDgzQTE5MEUwM0YxNUFGNTllOUJDMmYxMzQ3NjRmYTRlMDNBOJSMEHRyYW5zYWN0aW9uSW5kZXiUSwCMBXZhbHVllEsAjAR0eXBllIwDMHgwlIwBdpRLQYwBcpRoCEMgfMXHGqRYF/sVrjfCj/Wt5Tzli7cPJUjzYcjnW/+aH4KUhZSBlIwBc5RoCEMgK+S+3JxJzyKAebiTCUsB87AiXF40Xk2i413VeZ7+q6yUhZSBlHViLg==','New')\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"INSERT INTO transaction values('0xdE3483A190E03F15AF59e9BC2f134764fa4e03A8','0xdE3483A190E03F15AF59e9BC2f134764fa4e03A8','-7807955977915799603','QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD','Guangzhou',9624,'speed,gps,sensor,accident,network','0x6bB0c13AA3bfCd998193C1198E0C40A4f0e1ea41',0.4809053012430742,'gASVPQQAAAAAAACME3dlYjMuZGF0YXN0cnVjdHVyZXOUjA1BdHRyaWJ1dGVEaWN0lJOUKYGUfZQojAlibG9ja0hhc2iUjA1oZXhieXRlcy5tYWlulIwISGV4Qnl0ZXOUk5RDIPimBxfIQXGlMLBqKD4R58IfvLeyHV63NBrE17ShDOBmlIWUgZSMC2Jsb2NrTnVtYmVylE3+F4wEZnJvbZSMKjB4ZEUzNDgzQTE5MEUwM0YxNUFGNTllOUJDMmYxMzQ3NjRmYTRlMDNBOJSMA2dhc5RKUCYCAIwIZ2FzUHJpY2WUSgDKmjuMBGhhc2iUaAhDIJYwuveZrdBUz+rfZS9l/ZuyJjnUODw7tiOIor83E6j5lIWUgZSMBWlucHV0lFhWAgAAMHhmZmZmODAwNDk1MWQwMTAwMDAwMDAwMDAwMDdkOTQyODhjMDQ1NDc5NzA2NTk0OGMwYzU2NjU2ODY5NjM2YzY1MjA0NDYxNzQ2MTk0OGMwMzU2NDk0ZTk0OGMxNDJkMzczODMwMzczOTM1MzUzOTM3MzczOTMxMzUzNzM5MzkzNjMwMzM5NDhjMDg0YzZmNjM2MTc0Njk2ZjZlOTQ4YzA5NDc3NTYxNmU2NzdhNjg2Zjc1OTQ4YzBhNTI2NTYzNmY3MjY0NWY2ZTc1NmQ5NDRkOTgyNThjMTA0MzZmNmU3NDcyNjE2Mzc0NWY2MTY0NjQ3MjY1NzM3Mzk0OGMyYTMwNzgzNjYyNDIzMDYzMzEzMzQxNDEzMzYyNjY0MzY0MzkzOTM4MzEzOTMzNDMzMTMxMzkzODQ1MzA0MzM0MzA0MTM0NjYzMDY1MzE2NTYxMzQzMTk0OGMwNTUwNzI2OTYzNjU5NDQ3M2ZkZWM3MjcwNzUzZjg1NDhjMDQ0NjY5NmM2NTk0OGMyZTUxNmQ1NjQzNWE2NTRlNTIzNzY1NTE0ZTQ1NzUzNTQ3NjU2YjcxNzE2MjZlNmQ2YjM4MzU3NjM2MzY2MzQ2NDg0ODZhNWE1YTU0NDc1YTc4NDE3MTQxMzI2ODQ0OTQ4YzBiNDQ2NTczNjM3MjY5NzA3NDY5NmY2ZTk0NWQ5NDhjMjE3MzcwNjU2NTY0MmM2NzcwNzMyYzczNjU2ZTczNmY3MjJjNjE2MzYzNjk2NDY1NmU3NDJjNmU2NTc0Nzc2ZjcyNmI5NDYxNzUyZZSMBW5vbmNllEsDjAJ0b5SMKjB4ZEUzNDgzQTE5MEUwM0YxNUFGNTllOUJDMmYxMzQ3NjRmYTRlMDNBOJSMEHRyYW5zYWN0aW9uSW5kZXiUSwCMBXZhbHVllEsAjAR0eXBllIwDMHgwlIwBdpRLQYwBcpRoCEMgfMXHGqRYF/sVrjfCj/Wt5Tzli7cPJUjzYcjnW/+aH4KUhZSBlIwBc5RoCEMgK+S+3JxJzyKAebiTCUsB87AiXF40Xk2i413VeZ7+q6yUhZSBlHViLg==','New')\""
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlstr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(database=\"ether\", user=\"postgres\",password=\"postgres\", host=\"127.0.0.1\", port=\"5432\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "transaction_table_sql = 'create table transaction ('+\\\n",
    "    'id serial primary key, from_addr varchar(160), to_addr varchar(160), vin_id varchar(160), '+\\\n",
    "    'file varchar(300), location varchar(160), record_num int, metadata varchar(300), '+\\\n",
    "    'contract_addr varchar(160), price real, rawdata text, status varchar(80), '+\\\n",
    "    'password varchar(160), reviews text)'\n",
    "cursor.execute(transaction_table_sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('create table transaction (id serial, from_addr varchar(160), to_addr varchar(160), vin_id varchar(160), file varchar(300), record_num int, location varchar(160), date_from timestamp, date_to timestamp, metadata varchar(300), contract_addr varchar(160), price real, rawdata text, status varchar(80), password varchar(160), reviews text)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "w3.geth.personal.unlockAccount(w3.eth.accounts[0], 'abc12345')\n",
    "metadata = {}\n",
    "metadata['Type'] = 'Vehicle Data'\n",
    "metadata['VIN'] = 'abcdefg'\n",
    "metadata['Location'] = 'Guangzhou'\n",
    "metadata['Contract_address'] = '0x714FeF968203d0b002199b7303A6fb490a406CC1'\n",
    "metadata['Price'] = 2.0\n",
    "metadata['File'] = 'QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD'\n",
    "metadata['Description'] = ['speed,gps,sensor,accident,network']\n",
    "m = b'\\xFF\\xFF' + pickle.dumps(metadata)\n",
    "tx_hash = w3.eth.sendTransaction({'from':w3.eth.accounts[0],'to':w3.eth.accounts[0],'value':Web3.toWei(0, 'ether'), 'data':m})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "entries = block_filter.get_new_entries()\n",
    "for entry in entries:\n",
    "    tx_counts = w3.eth.get_block_transaction_count(entry)\n",
    "    for i in range(tx_counts):\n",
    "        tx = w3.eth.get_transaction_by_block(entry, i)\n",
    "        if tx['input'][:6]=='0xffff':\n",
    "            data = Web3.toBytes(hexstr=tx['input'][6:])\n",
    "            data = pickle.loads(data)\n",
    "            sqlstr = \"INSERT INTO transaction values(\"+\\\n",
    "                \"'\"+tx['from']+\"',\"+\\\n",
    "                \"'\"+tx['to']+\"',\"+\\\n",
    "                \"'\"+data['VIN']+\"',\"+\\\n",
    "                \"'\"+data['File']+\"',\"+\\\n",
    "                \"'\"+data['Location']+\"',\"+\\\n",
    "                \"'\"+','.join(data['Description'])+\"',\"+\\\n",
    "                \"'\"+data['Contract_address']+\"',\"+\\\n",
    "                str(data['Price'])+\",\"+\\\n",
    "                \"'\"+str(base64.b64encode(pickle.dumps(tx)), encoding='utf-8')+\"')\"\n",
    "            cursor.execute(sqlstr)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('create table transaction (id serial, from_addr varchar(160), to_addr varchar(160), vin_id varchar(160), file varchar(300), location varchar(160), date_from timestamp, data_to timestamp, metadata varchar(300), contract_addr varchar(160), price real, rawdata text, status varchar(80), password varchar(160), reviews text)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"INSERT INTO transaction values('0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8','0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8','Guangzhou','speed,gps,sensor,accident,network','0x714FeF968203d0b002199b7303A6fb490a406CC1','gASVAwQAAAAAAACME3dlYjMuZGF0YXN0cnVjdHVyZXOUjA1BdHRyaWJ1dGVEaWN0lJOUKYGUfZQojAlibG9ja0hhc2iUjA1oZXhieXRlcy5tYWlulIwISGV4Qnl0ZXOUk5RDIDgxgU7Nsgzn7AaWGEHLG5oSxFhCG7a/AUN2Ipu+EcthlIWUgZSMC2Jsb2NrTnVtYmVylE2KFIwEZnJvbZSMKjB4YjZiRUJmQmJDN0U1RUUxRGJmNTYxMmYyQzI5ODM3MEU5NEM2OWRiOJSMA2dhc5RKnBwCAIwIZ2FzUHJpY2WUSgDKmjuMBGhhc2iUaAhDIMha7CXsT4yLRWbWRYARVxzJDJkMxc1SoEpHivmN4i+SlIWUgZSMBWlucHV0lFgcAgAAMHhmZmZmODAwNDk1MDAwMTAwMDAwMDAwMDAwMDdkOTQyODhjMDQ1NDc5NzA2NTk0OGMwYzU2NjU2ODY5NjM2YzY1MjA0NDYxNzQ2MTk0OGMwMzU2NDk0ZTk0OGMwNzYxNjI2MzY0NjU2NjY3OTQ4YzA4NGM2ZjYzNjE3NDY5NmY2ZTk0OGMwOTQ3NzU2MTZlNjc3YTY4NmY3NTk0OGMxMDQzNmY2ZTc0NzI2MTYzNzQ1ZjYxNjQ2NDcyNjU3MzczOTQ4YzJhMzA3ODM3MzEzNDQ2NjU0NjM5MzYzODMyMzAzMzY0MzA2MjMwMzAzMjMxMzkzOTYyMzczMzMwMzM0MTM2NjY2MjM0MzkzMDYxMzQzMDM2NDM0MzMxOTQ4YzA1NTA3MjY5NjM2NTk0NDc0MDAwMDAwMDAwMDAwMDAwOGMwNDQ2Njk2YzY1OTQ4YzJlNTE2ZDU2NDM1YTY1NGU1MjM3NjU1MTRlNDU3NTM1NDc2NTZiNzE3MTYyNmU2ZDZiMzgzNTc2MzYzNjYzNDY0ODQ4NmE1YTVhNTQ0NzVhNzg0MTcxNDEzMjY4NDQ5NDhjMGI0NDY1NzM2MzcyNjk3MDc0Njk2ZjZlOTQ1ZDk0OGMyMTczNzA2NTY1NjQyYzY3NzA3MzJjNzM2NTZlNzM2ZjcyMmM2MTYzNjM2OTY0NjU2ZTc0MmM2ZTY1NzQ3NzZmNzI2Yjk0NjE3NTJllIwFbm9uY2WUSy6MAnRvlIwqMHhiNmJFQmZCYkM3RTVFRTFEYmY1NjEyZjJDMjk4MzcwRTk0QzY5ZGI4lIwQdHJhbnNhY3Rpb25JbmRleJRLAIwFdmFsdWWUSwCMBHR5cGWUjAMweDCUjAF2lEtCjAFylGgIQyDmM0sbTqTAeyVtZMnljEDPKprky9q4hBQM4n21V82EE5SFlIGUjAFzlGgIQyAsbPaiCqkOkuoiCMyN4Q9780ldnJyKUPxvmqm5eQnJo5SFlIGUdWIu')\""
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlstr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlstr = \"INSERT INTO transaction values(\"+\\\n",
    "                \"'\"+tx['from']+\"',\"+\\\n",
    "                \"'\"+tx['to']+\"',\"+\\\n",
    "                \"'\"+data['Location']+\"',\"+\\\n",
    "                \"'\"+','.join(data['Description'])+\"',\"+\\\n",
    "                \"'\"+data['Contract_address']+\"',\"+\\\n",
    "                \"'\"+str(base64.b64encode(pickle.dumps(tx)), encoding='utf-8')+\"')\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "ename": "UndefinedTable",
     "evalue": "错误:  关系 \"ether\" 不存在\nLINE 1: INSERT INTO ether values('0xb6bEBfBbC7E5EE1Dbf5612f2C298370E...\n                    ^\n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mUndefinedTable\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-149-7b33af1374a2>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlstr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mUndefinedTable\u001b[0m: 错误:  关系 \"ether\" 不存在\nLINE 1: INSERT INTO ether values('0xb6bEBfBbC7E5EE1Dbf5612f2C298370E...\n                    ^\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(sqlstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AttributeDict({'blockHash': HexBytes('0x71be0e07cb0ab3625a4029125891c8046f63296f8344e01cce40dba2efdc7447'),\n",
       " 'blockNumber': 4971,\n",
       " 'from': '0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8',\n",
       " 'gas': 135744,\n",
       " 'gasPrice': 1000000000,\n",
       " 'hash': HexBytes('0x8dd1cf8e65f470c01e800fc4e9515faaa3a91dc0f2c29fb90ef04fab4f04e784'),\n",
       " 'input': '0xffff800495d9000000000000007d94288c0454797065948c0c56656869636c652044617461948c10436f6e74726163745f61646472657373948c2a307837313446654639363832303364306230303231393962373330334136666234393061343036434331948c055072696365944740000000000000008c0446696c65948c2e516d56435a654e523765514e45753547656b7171626e6d6b3835763636634648486a5a5a54475a78417141326844948c0b4465736372697074696f6e945d948c2173706565642c6770732c73656e736f722c6163636964656e742c6e6574776f726b9461752e',\n",
       " 'nonce': 34,\n",
       " 'to': '0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8',\n",
       " 'transactionIndex': 0,\n",
       " 'value': 0,\n",
       " 'type': '0x0',\n",
       " 'v': 65,\n",
       " 'r': HexBytes('0x935f003dd8dc26cce03262af7aaef6451bdd469d75a6c4dff1b2df3c6a02cfa9'),\n",
       " 's': HexBytes('0x436ac751bac61db81d96bda1e87f0d99a5e8cb7ed17ad6cf7f15d3ef7a34f2a5')})"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w3.eth.get_transaction(tx_hash)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "0x800495a1000000000000007d94288c0454797065948c0c56656869636c652044617461948c10436f6e74726163745f61646472657373948c2a307837313446654639363832303364306230303231393962373330334136666234393061343036434331948c055072696365944740000000000000008c0b4465736372697074696f6e945d948c2173706565642c6770732c73656e736f722c6163636964656e742c6e6574776f726b9461752e"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata = {}\n",
    "metadata['Type'] = 'Vehicle Data'\n",
    "metadata['']\n",
    "metadata['Contract_address'] = '0x714FeF968203d0b002199b7303A6fb490a406CC1'\n",
    "metadata['Price'] = 2.0\n",
    "metadata['File'] = 'QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD'\n",
    "metadata['Description'] = ['speed,gps,sensor,accident,network']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = pickle.dumps(metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'\\x80\\x04\\x95\\xd9\\x00\\x00\\x00\\x00\\x00\\x00\\x00}\\x94(\\x8c\\x04Type\\x94\\x8c\\x0cVehicle Data\\x94\\x8c\\x10Contract_address\\x94\\x8c*0x714FeF968203d0b002199b7303A6fb490a406CC1\\x94\\x8c\\x05Price\\x94G@\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x8c\\x04File\\x94\\x8c.QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD\\x94\\x8c\\x0bDescription\\x94]\\x94\\x8c!speed,gps,sensor,accident,network\\x94au.'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "a=b'\\xFF\\xFF'+m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a[:2] == b'\\xFF\\xFF'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'\\x80\\x04\\x95\\xd9\\x00\\x00\\x00\\x00\\x00\\x00\\x00}\\x94(\\x8c\\x04Type\\x94\\x8c\\x0cVehicle Data\\x94\\x8c\\x10Contract_address\\x94\\x8c*0x714FeF968203d0b002199b7303A6fb490a406CC1\\x94\\x8c\\x05Price\\x94G@\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x8c\\x04File\\x94\\x8c.QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD\\x94\\x8c\\x0bDescription\\x94]\\x94\\x8c!speed,gps,sensor,accident,network\\x94au.'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a[2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "#block_filter = w3.eth.filter({'fromBlock':1, 'toBlock':w3.eth.get_block_number()})\n",
    "block_filter = w3.eth.filter('latest')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_filter.get_new_entries()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5016"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w3.eth.get_block_number()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w3.eth.get_block_transaction_count(4971)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AttributeDict({'blockHash': HexBytes('0x855a48203048645a5f51de355649345ce56e342bf5f2375492f469505f9168cd'), 'blockNumber': 5052, 'from': '0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8', 'gas': 135744, 'gasPrice': 1000000000, 'hash': HexBytes('0x48a9745f16e6dfc1e9b24f213ff8de07584efb363f54c985a55713f3dafda3e9'), 'input': '0xffff800495d9000000000000007d94288c0454797065948c0c56656869636c652044617461948c10436f6e74726163745f61646472657373948c2a307837313446654639363832303364306230303231393962373330334136666234393061343036434331948c055072696365944740000000000000008c0446696c65948c2e516d56435a654e523765514e45753547656b7171626e6d6b3835763636634648486a5a5a54475a78417141326844948c0b4465736372697074696f6e945d948c2173706565642c6770732c73656e736f722c6163636964656e742c6e6574776f726b9461752e', 'nonce': 35, 'to': '0xb6bEBfBbC7E5EE1Dbf5612f2C298370E94C69db8', 'transactionIndex': 0, 'value': 0, 'type': '0x0', 'v': 65, 'r': HexBytes('0x52eba6c23ec9580fd8b4757fab797ccbaa4dc07a425dc7ae2b7b0183c17ccc51'), 's': HexBytes('0x4e9669622b2b6b0ad4c63236d41b1d92413a535e70631189c50ccbf754e1d776')})\n"
     ]
    }
   ],
   "source": [
    "entries = block_filter.get_new_entries()\n",
    "for entry in entries:\n",
    "    tx_counts = w3.eth.get_block_transaction_count(entry)\n",
    "    for i in range(tx_counts):\n",
    "        tx = w3.eth.get_transaction_by_block(entry, i)\n",
    "        print(tx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = '0xffff800495d9000000000000007d94288c0454797065948c0c56656869636c652044617461948c10436f6e74726163745f61646472657373948c2a307837313446654639363832303364306230303231393962373330334136666234393061343036434331948c055072696365944740000000000000008c0446696c65948c2e516d56435a654e523765514e45753547656b7171626e6d6b3835763636634648486a5a5a54475a78417141326844948c0b4465736372697074696f6e945d948c2173706565642c6770732c73656e736f722c6163636964656e742c6e6574776f726b9461752e'\n",
    "data = Web3.toBytes(hexstr=data[6:])\n",
    "a = pickle.loads(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Type': 'Vehicle Data',\n",
       " 'Contract_address': '0x714FeF968203d0b002199b7303A6fb490a406CC1',\n",
       " 'Price': 2.0,\n",
       " 'File': 'QmVCZeNR7eQNEu5Gekqqbnmk85v66cFHHjZZTGZxAqA2hD',\n",
       " 'Description': ['speed,gps,sensor,accident,network']}"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[HexBytes('0xef3f5be162adb0dedaeb0d8c106ef8eab0161f71fabf084c5481e5904a349e9d'),\n",
       " HexBytes('0x648c05e0add5c713b65a578a1b93a5275450b780c888578633ff075624f71593'),\n",
       " HexBytes('0xeb4d7670ed20c3e9a832dbcc7d1a9d0d83e5f3b9e0fb19b18fc352e2ddb08f77'),\n",
       " HexBytes('0x454058c26da87128fd1f09800132ef05f3aa07e60af0716b1b06b7bf3ca98ff7')]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"INSERT INTO ether(1,'haha')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "b = w3.eth.get_transaction(tx_hash)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'gASVAwQAAAAAAACME3dlYjMuZGF0YXN0cnVjdHVyZXOUjA1BdHRyaWJ1dGVEaWN0lJOUKYGUfZQojAlibG9ja0hhc2iUjA1oZXhieXRlcy5tYWlulIwISGV4Qnl0ZXOUk5RDINApIwXEmfsI6sjKbl38ifVS37MLIMgVWZFlXHKpwG7+lIWUgZSMC2Jsb2NrTnVtYmVylE3rE4wEZnJvbZSMKjB4YjZiRUJmQmJDN0U1RUUxRGJmNTYxMmYyQzI5ODM3MEU5NEM2OWRiOJSMA2dhc5RKnBwCAIwIZ2FzUHJpY2WUSgDKmjuMBGhhc2iUaAhDIEYRdRvZQCyq0Coz0uazafFFlzMei+Bd2kz2sa0w+gaFlIWUgZSMBWlucHV0lFgcAgAAMHhmZmZmODAwNDk1MDAwMTAwMDAwMDAwMDAwMDdkOTQyODhjMDQ1NDc5NzA2NTk0OGMwYzU2NjU2ODY5NjM2YzY1MjA0NDYxNzQ2MTk0OGMwMzU2NDk0ZTk0OGMwNzYxNjI2MzY0NjU2NjY3OTQ4YzA4NGM2ZjYzNjE3NDY5NmY2ZTk0OGMwOTQ3NzU2MTZlNjc3YTY4NmY3NTk0OGMxMDQzNmY2ZTc0NzI2MTYzNzQ1ZjYxNjQ2NDcyNjU3MzczOTQ4YzJhMzA3ODM3MzEzNDQ2NjU0NjM5MzYzODMyMzAzMzY0MzA2MjMwMzAzMjMxMzkzOTYyMzczMzMwMzM0MTM2NjY2MjM0MzkzMDYxMzQzMDM2NDM0MzMxOTQ4YzA1NTA3MjY5NjM2NTk0NDc0MDAwMDAwMDAwMDAwMDAwOGMwNDQ2Njk2YzY1OTQ4YzJlNTE2ZDU2NDM1YTY1NGU1MjM3NjU1MTRlNDU3NTM1NDc2NTZiNzE3MTYyNmU2ZDZiMzgzNTc2MzYzNjYzNDY0ODQ4NmE1YTVhNTQ0NzVhNzg0MTcxNDEzMjY4NDQ5NDhjMGI0NDY1NzM2MzcyNjk3MDc0Njk2ZjZlOTQ1ZDk0OGMyMTczNzA2NTY1NjQyYzY3NzA3MzJjNzM2NTZlNzM2ZjcyMmM2MTYzNjM2OTY0NjU2ZTc0MmM2ZTY1NzQ3NzZmNzI2Yjk0NjE3NTJllIwFbm9uY2WUSySMAnRvlIwqMHhiNmJFQmZCYkM3RTVFRTFEYmY1NjEyZjJDMjk4MzcwRTk0QzY5ZGI4lIwQdHJhbnNhY3Rpb25JbmRleJRLAIwFdmFsdWWUSwCMBHR5cGWUjAMweDCUjAF2lEtBjAFylGgIQyAjLbUid2vvmmCSg1GXOBR8bvVn4FFioge6ALkprFvgF5SFlIGUjAFzlGgIQyAQg43XqnnfJNHqJT0exUGB91CgPyx517tDHALoGz5/R5SFlIGUdWIu'"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(base64.b64encode(pickle.dumps(b)), encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
